import datetime
import uuid
from io import BytesIO

import openpyxl
from django.contrib.auth.models import User
from django.core.paginator import Paginator
from django.db.models import Q
from django.forms import model_to_dict
from django.http import HttpResponse
from openpyxl.styles import Alignment
from openpyxl.utils import get_column_letter

from defect.models import Defect
from etdata.models import Etdata
from feedback.models import FeedBack
from isi_api.settings import API_HOST, BASE_DIR
from liscense.models import Liscense, Member


def query_to_list(query):
    querylist = []
    for item in query:
        querylist.append(model_to_dict(item))
    return querylist

# 按照证书排列的方式，将证书的queryset转换为列表


def liscense_to_list(liscenses):
    liscenseList = []
    for liscense in liscenses:
        liscense_dict = {}
        liscense_dict['id'] = liscense.id
        liscense_dict['member'] = model_to_dict(liscense.member)
        liscense_dict['name'] = liscense.name
        liscense_dict['level'] = liscense.level
        liscense_dict['organ'] = liscense.organ
        liscense_dict['start_date'] = liscense.start_date.strftime('%Y-%m-%d')
        liscense_dict['empire_date'] = liscense.empire_date.strftime(
            '%Y-%m-%d')
        liscense_dict['is_confirmed'] = liscense.is_confirmed
        if liscense.confirm_user:
            liscense_dict['confirm_user'] = model_to_dict(
                liscense.confirm_user)
        else:
            liscense_dict['confirm_user'] = None
        pdf_file = {}
        if liscense.pdffile.name:
            pdf_file['pdffile_path'] = API_HOST + \
                '/media/' + liscense.pdffile.name
            pdf_file['filename'] = liscense.pdffile.name.split(
                '\\')[-1].split('/')[-1]
            liscense_dict['pdffile'] = pdf_file
        else:
            pdf_file['pdffile_path'] = ''
            pdf_file['filename'] = ''
            liscense_dict['pdffile'] = ''
        liscenseList.append(liscense_dict)
    return liscenseList

# 以人员为主的证书排列方式，将证书的queryset转换为列表


def liscense_to_list2(members):
    memberList = []
    for member in members:
        # if member.liscense_set.filter(is_delete=False).count():
        member_obj = {}
        member_obj['id'] = member.id
        member_obj['name'] = member.name
        member_obj['p_num'] = member.p_num
        member_obj['id_num'] = member.id_num
        member_obj['company'] = member.company
        member_obj['role'] = member.role
        member_liscenses = Liscense.objects.filter(
            member=member).filter(is_delete=False)
        liscenses_list = []
        for liscense in member_liscenses:
            liscense_obj = {}
            liscense_obj['id'] = liscense.id
            liscense_obj['name'] = liscense.name
            liscense_obj['level'] = liscense.level
            liscense_obj['organ'] = liscense.organ
            liscense_obj['start_date'] = liscense.start_date.strftime(
                '%Y-%m-%d')
            liscense_obj['empire_date'] = liscense.empire_date.strftime(
                '%Y-%m-%d')
            liscense_obj['is_confirmed'] = liscense.is_confirmed
            if liscense.confirm_user:
                liscense_obj['confirm_user'] = model_to_dict(
                    liscense.confirm_user)
            else:
                liscense_obj['confirm_user'] = None
            pdf_file = {}
            if liscense.pdffile.name:
                pdf_file['pdffile_path'] = API_HOST + liscense.pdffile.url
                pdf_file['filename'] = liscense.pdffile.name.split('\\')[-1]
                liscense_obj['pdffile'] = pdf_file
            else:
                pdf_file['pdffile_path'] = ''
                pdf_file['filename'] = ''
                liscense_obj['pdffile'] = ''
            liscenses_list.append(liscense_obj)
        member_obj['liscenses'] = liscenses_list
        memberList.append(member_obj)
    return memberList

# 根据条件查询memebrs


def member_filter(queryInfo):
    query = queryInfo.get('query').upper()
    memberIdStr = queryInfo.get('memberIdStr')
    con = Q()
    if query:
        q1 = Q(name__contains=query)
        q2 = Q(p_num__contains=query)
        q3 = Q(id_num__contains=query)
        q4 = Q(company__contains=query)
        con.add(q1, 'OR')
        con.add(q2, 'OR')
        con.add(q3, 'OR')
        con.add(q4, 'OR')
    if memberIdStr:
        memberIdList = memberIdStr.split(',')
        q5 = Q(id__in=memberIdList)
        con.add(q5, 'AND')
    members = Member.objects.filter(is_delete=False).filter(con)
    return members


# 获取文件名的时间后缀文本
def gettimestr():
    now = datetime.datetime.now().strftime('%Y%m%d')
    return now
# 将members（querySet）处理为excel的url


def member_to_excelpath(members):
    wb = openpyxl.Workbook()
    sheet = wb.active
    sheet.title = '成员证书清单'
    titles = ['序号', '姓名', '员工号', '身份证号', '公司', '证书', '发证机关',
              '起始有效期', '证书到期时间', '是否被审核', '审核人员', '证书文件地址']
    sheet.append(titles)
    index = 1
    for member in members:
        member_list = []
        member_list.append(index)
        index = index + 1
        member_list.append(member.name)
        member_list.append(member.p_num)
        member_list.append(member.id_num)
        member_list.append(member.company)
        member_liscenses = member.liscense_set.all()
        if not member_liscenses.exists():
            sheet.append(member_list)
        for member_liscense in member_liscenses:
            liscense_list = []
            liscense_list.append(member_liscense.name + member_liscense.level)
            liscense_list.append(member_liscense.organ)
            liscense_list.append(member_liscense.start_date)
            liscense_list.append(member_liscense.empire_date)
            liscense_list.append(member_liscense.is_confirmed)
            if member_liscense.confirm_user:
                liscense_list.append(member_liscense.confirm_user.username)
            else:
                liscense_list.append('')
            if member_liscense.pdffile:
                pdffile_path = API_HOST + '/media/' + member_liscense.pdffile.name
                liscense_list.append(pdffile_path)
            else:
                liscense_list.append('')
            sheet.append(member_list + liscense_list)

    # 宽度暂未调整
    width_list = [5, 8, 8, 20, 8, 8, 18, 16, 16, 12, 12, 80]
    for i in range(12):
        sheet.column_dimensions[get_column_letter(i + 1)].width = width_list[i]
    relative_path = '/media/exceltemp/' + '证书清单' + \
        gettimestr() + '-' + uuid.uuid4().hex + '.xlsx'
    excel_path = BASE_DIR + relative_path
    wb.save(excel_path)
    excel_url = API_HOST + relative_path
    return excel_url

# 根据条件查询feedbacks


def feedback_filter(queryInfo):
    query = queryInfo.get('query').upper()
    feedbacks = FeedBack.objects.filter(is_delete=False).order_by("-occurDate")
    if query:
        con = Q()
        q1 = Q(outage__contains=query)
        q2 = Q(team__contains=query)
        q3 = Q(title__contains=query)
        q4 = Q(detail__contains=query)
        q5 = Q(reason__contains=query)
        con.add(q1, 'OR')
        con.add(q2, 'OR')
        con.add(q3, 'OR')
        con.add(q4, 'OR')
        con.add(q5, 'OR')
        feedbacks = feedbacks.filter(con)
    return feedbacks
# feedback导出到excel的path


def feedback_to_excelpath(feedbacks):
    wb = openpyxl.Workbook()
    sheet = wb.active
    titles = ['序号', '大修轮次', '项目', '事件标题',
              '事件描述', '发生日期', '原因分析', '反馈措施', '录入人']
    sheet.append(titles)
    index = 1
    for feedback in feedbacks:
        feedback_list = [index]
        feedback_list.append(feedback.outage)
        feedback_list.append(feedback.team)
        feedback_list.append(feedback.title)
        feedback_list.append(feedback.detail)
        feedback_list.append(feedback.occurDate)
        feedback_list.append(feedback.reason)
        feedback_list.append(feedback.measure)
        feedback_list.append(feedback.create_user)
        sheet.append(feedback_list)
        index = index + 1

    # 设置单元格的自动换行
    alignment = Alignment(wrap_text=True, vertical='center', horizontal='left')
    for row in sheet.rows:
        for cell in row:
            cell.alignment = alignment
    # 设置单元格宽度
    width_list = [5, 10, 10, 60, 60, 15, 60, 60, 15]
    for i in range(9):
        sheet.column_dimensions[get_column_letter(i + 1)].width = width_list[i]
    relative_path = '/media/exceltemp/' + '经验反馈清单' + \
        gettimestr() + '-' + uuid.uuid4().hex + '.xlsx'
    excel_path = BASE_DIR + relative_path
    wb.save(excel_path)
    excel_url = API_HOST + relative_path
    return excel_url

# 根据get数据查询缺陷信息


def defect_filter(queryInfo):
    query = queryInfo.get('query').strip()
    con = Q()
    if query:
        q1 = Q(outage__contains=query)
        q2 = Q(main_order__contains=query)
        q3 = Q(order_detail__contains=query)
        q4 = Q(defect_detail__contains=query)
        q5 = Q(equipment__contains=query)
        q6 = Q(defect_order__contains=query)
        q7 = Q(deal_department__contains=query)
        q8 = Q(team__contains=query)
        q9 = Q(deal_order__contains=query)
        q10 = Q(deal_detail__contains=query)
        con.add(q1, 'OR')
        con.add(q2, 'OR')
        con.add(q3, 'OR')
        con.add(q4, 'OR')
        con.add(q5, 'OR')
        con.add(q6, 'OR')
        con.add(q7, 'OR')
        con.add(q8, 'OR')
        con.add(q9, 'OR')
        con.add(q10, 'OR')
    q11 = Q(is_delete=False)
    con.add(q11, 'AND')
    defects = Defect.objects.filter(con)
    return defects

# 导出缺陷的excel


def defect_to_excelpath(defects):
    wb = openpyxl.Workbook()
    sheet = wb.active
    titles = ['序号', '大修轮次', '主票号', '主票描述', '缺陷描述', '功能位置', '缺陷通知单',
              '缺陷日期', '处理工单/QDR',  '处理详情', '是否关闭', '关闭日期', '责任部门', '检查专业']
    sheet.append(titles)
    index = 1
    for defect in defects:
        defect_list = [index, defect.outage, defect.main_order, defect.order_detail,
                       defect.defect_detail, defect.equipment, defect.defect_order, defect.defect_date]
        if defect.deal_order:
            defect_list.append(defect.deal_order)
        else:
            defect_list.append('')

        if defect.deal_detail:
            defect_list.append(defect.deal_detail)
        else:
            defect_list.append('')

        if defect.is_closed:
            defect_list.append('是')
            defect_list.append(defect.closed_date)
        else:
            defect_list.append('否')
            defect_list.append('')
        defect_list.append(defect.deal_department)
        defect_list.append(defect.team)
        sheet.append(defect_list)
        index = index + 1

    # 设置单元格的自动换行
    alignment = Alignment(wrap_text=True, vertical='center', horizontal='left')
    for row in sheet.rows:
        for cell in row:
            cell.alignment = alignment

    # 设置单元格宽度
    width_list = [8, 10, 15, 50, 70, 20, 15, 15, 15, 50, 10, 15, 10, 15]
    for i in range(14):
        sheet.column_dimensions[get_column_letter(i + 1)].width = width_list[i]
    relative_path = '/media/exceltemp/' + '缺陷统计清单' + \
        gettimestr() + '-' + uuid.uuid4().hex + '.xlsx'
    excel_path = BASE_DIR + relative_path
    wb.save(excel_path)
    excel_url = API_HOST + relative_path
    return excel_url

# 将涡流数据的字典转换为model


def etdata_dict_to_model(etdata):
    # print(etdata)
    new_etdata = Etdata()
    new_etdata.outage = etdata['outage']
    new_etdata.tank = etdata['tank']
    new_etdata.zone = etdata['zone']
    new_etdata.row = etdata['row']
    new_etdata.col = etdata['col']
    new_etdata.cal = etdata['cal']
    new_etdata.ext = etdata['ext']
    new_etdata.leg = etdata['leg']
    new_etdata.code = etdata['code']
    if etdata['volt'] is not '':
        new_etdata.volt = etdata['volt']
    if etdata['ph'] is not '':
        new_etdata.ph = etdata['ph']
    new_etdata.ch = etdata['ch']
    if etdata['percent'] is not '':
        new_etdata.percent = etdata['percent']
    new_etdata.sup = etdata['sup']
    if etdata['dist'] is not '':
        new_etdata.dist = etdata['dist']
    new_etdata.sup2 = etdata['sup2']
    if etdata['dist2'] is not '':
        new_etdata.dist2 = etdata['dist2']
    new_etdata.long = etdata['long']
    new_etdata.probe = etdata['probe']
    new_etdata.arc = etdata['arc']
    new_etdata.noise = etdata['noise']
    new_etdata.analyst = etdata['analyst']
    new_etdata.date = datetime.datetime.strptime(
        etdata['date'], '%Y-%m-%dT%H:%M:%S')
    new_etdata.analysis = etdata['analysis']
    if etdata['priority'] is not '':
        new_etdata.priority = etdata['priority']
    return new_etdata

# 根据query条件查询涡流数据，并返回json 格式的结果


def etdata_filter(query):
    outages = query.get('outages')
    tanks = query.get('tanks')
    codes = query.get('codes')
    con = Q()
    q1 = Q(outage__in=outages)
    q2 = Q(tank__in=tanks)
    q3 = Q(code__in=codes)
    if len(outages):
        con.add(q1, 'AND')
    if len(tanks):
        con.add(q2, 'AND')
    if len(codes):
        con.add(q3, 'AND')
    etdatas_query = Etdata.objects.filter(con)
    return etdatas_query

# 根据etdatas的查询集导出excel_url


def etdata_to_excel(etdatas):
    wb = openpyxl.Workbook()
    ws = wb.active
    titles = ['序号', '大修', '容器', 'ZONE', 'ROW', 'COL', 'CAL', 'EXT', 'LEG', 'CODE', 'VOLT', 'PH', 'CH', '%',
              'SUP', 'DIST', 'SUP2', 'DIST2', 'LONG', 'PROBE', 'ARC', 'NOISE', 'ANALYST', 'DATE', 'ANALYSIS', 'PRIORITY']
    ws.append(titles)
    index = 1
    for etdata in etdatas:
        etdata_row = [index, etdata.outage, etdata.tank, etdata.zone, etdata.row, etdata.col, etdata.cal, etdata.ext, etdata.leg, etdata.code,
                      etdata.volt, etdata.ph, etdata.ch, etdata.percent, etdata.sup, etdata.dist, etdata.sup2, etdata.dist2, etdata.long, etdata.probe, etdata.arc]
        etdata_row.append(etdata.noise)
        etdata_row.append(etdata.analyst)
        etdata_row.append(etdata.date)
        etdata_row.append(etdata.analysis)
        etdata_row.append(etdata.priority)
        etdata_row[15] = dist_to_string(etdata.dist)
        etdata_row[17] = dist_to_string(etdata.dist2)
        ws.append(etdata_row)
        index = index + 1
    # 以下设置表格格式
    # 设置单元格的自动换行
    alignment = Alignment(vertical='center', horizontal='center')
    for row in ws.rows:
        for cell in row:
            cell.alignment = alignment
    # 设置单元格的宽度
    width_list = [6, 8, 12, 6, 6, 6, 20, 8, 5, 6, 8, 6,
                  13, 6, 6, 8, 6, 6, 6, 6, 6, 6, 6, 20, 10, 10]
    for i in range(26):
        ws.column_dimensions[get_column_letter(i + 1)].width = width_list[i]

    relative_path = '/media/exceltemp/' + '涡流数据导出清单' + \
        gettimestr() + '-' + uuid.uuid4().hex + '.xlsx'
    excel_path = BASE_DIR + relative_path
    wb.save(excel_path)
    excel_url = API_HOST + relative_path
    return excel_url

# 处理dist数据(将不足四位的补充为四位，中间带"-")


def dist_to_string(dist):
    if dist is None or dist == '':
        return dist
    dist_str = str(dist)
    if len(dist_str) == 1:
        dist_str = '000' + dist_str
    elif len(dist_str) == 3:
        dist_str = '0' + dist_str
    elif len(dist_str) == 4:
        pass
    else:
        return dist_str
    dist_str = dist_str[0:2] + '-' + dist_str[2:4]
    return dist_str
